import os
import io
import gzip
import pymysql
import logging
import signal
from datetime import datetime, date, timedelta, timezone
try:
    from zoneinfo import ZoneInfo
except ImportError:
    logging.warning("zoneinfo 模块未找到。时区支持可能受限。")
    class FixedZoneInfo(timezone):
        def __init__(self, offset, name):
            super().__init__(offset, name)
        def __repr__(self):
            return f"FixedZoneInfo(offset={self.utcoffset(None)}, name='{self.tzname(None)}')"
    SHANGHAI_TZ = FixedZoneInfo(timedelta(hours=8), "Asia/Shanghai_fixed_offset")
else:
    SHANGHAI_TZ = ZoneInfo("Asia/Shanghai")

from logging.handlers import RotatingFileHandler
import requests
from flask import Flask, render_template_string, request, jsonify, send_from_directory
from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.jobstores.sqlalchemy import SQLAlchemyJobStore
import json
import math
import subprocess
import threading
from functools import wraps
from time import time

# --- 数据库配置 ---
DB_HOST = os.getenv('DB_HOST', 'localhost')
DB_USER = os.getenv('DB_USER', 'root')
DB_PASSWORD = os.getenv('DB_PASSWORD', 'DB_PASSWORD')
DB_NAME = os.getenv('DB_NAME', 'gold_prices')

# --- 系统配置 ---
JOBSTORE_DB = os.getenv('JOBSTORE_DB', '/opt/gold/jobs.sqlite')
DEFAULT_INTERVAL = int(os.getenv('DEFAULT_INTERVAL', '300'))
MAX_RECORD_DAYS = int(os.getenv('MAX_RECORD_DAYS', '3650'))
HISTORY_ITEMS_PER_PAGE = 10
BACKUP_DIR = os.getenv('BACKUP_DIR', '/opt/gold/backups')
STATIC_DIR = os.getenv('STATIC_DIR', '/opt/gold/static')

# --- 数据源配置 ---
DEFAULT_SOURCES_CONFIG = {
    'ms': {
        'name': '民生银行',
        'api_url': os.getenv('API_URL_MS', 'https://api.jdjygold.com/gw/generic/hj/h5/m/latestPrice'),
        'path_to_price': ['resultData', 'datas', 'price'],
        'path_to_time': ['resultData', 'datas', 'time']
    },
    'zs': {
        'name': '浙商银行',
        'api_url': os.getenv('API_URL_ZS', 'https://api.jdjygold.com/gw2/generic/jrm/h5/m/stdLatestPrice?productSku=1961543816'),
        'path_to_price': ['resultData', 'datas', 'price'],
        'path_to_time': ['resultData', 'datas', 'time']
    },
    'icbc': {
        'name': '工商银行',
        'api_url': os.getenv('API_URL_ICBC', 'https://api.jdjygold.com/gw2/generic/jrm/h5/m/icbcLatestPrice?productSku=2005453243'),
        'path_to_price': ['resultData', 'datas', 'price'],
        'path_to_time': ['resultData', 'datas', 'time']
    }
}

SOURCES_CONFIG_JSON = os.getenv('SOURCES_CONFIG_JSON')
if SOURCES_CONFIG_JSON:
    try:
        SOURCES_CONFIG = json.loads(SOURCES_CONFIG_JSON)
        for key, config in SOURCES_CONFIG.items():
            if not all(k in config for k in ['name', 'api_url', 'path_to_price', 'path_to_time']):
                SOURCES_CONFIG = DEFAULT_SOURCES_CONFIG
                break
    except json.JSONDecodeError:
        SOURCES_CONFIG = DEFAULT_SOURCES_CONFIG
else:
    SOURCES_CONFIG = DEFAULT_SOURCES_CONFIG

# --- 日志配置优化 ---
def setup_logger():
    """配置日志系统"""
    logger = logging.getLogger('gold_monitor')
    logger.setLevel(logging.INFO)
    formatter = logging.Formatter('%(asctime)s %(levelname)s [%(funcName)s] %(message)s')
    
    os.makedirs('/opt/gold', exist_ok=True)
    os.makedirs(BACKUP_DIR, exist_ok=True)
    os.makedirs(STATIC_DIR, exist_ok=True)
    os.makedirs(os.path.join(STATIC_DIR, 'css'), exist_ok=True)
    os.makedirs(os.path.join(STATIC_DIR, 'js'), exist_ok=True)
    
    file_handler = RotatingFileHandler('/opt/gold/monitor.log', maxBytes=10*1024*1024, backupCount=5, encoding='utf-8')
    file_handler.setFormatter(formatter)
    
    stream_handler = logging.StreamHandler()
    stream_handler.setFormatter(formatter)
    
    logger.addHandler(file_handler)
    logger.addHandler(stream_handler)
    
    return logger

logger = setup_logger()
app = Flask(__name__)

# --- 数据库连接池 ---
class DatabasePool:
    """数据库连接池管理"""
    def __init__(self, max_connections=10):
        self.max_connections = max_connections
        self.connections = []
        self.lock = threading.Lock()
    
    def get_connection(self):
        """获取数据库连接"""
        with self.lock:
            if self.connections:
                return self.connections.pop()
        
        if not DB_PASSWORD:
            logger.error("DB_PASSWORD 环境变量未设置")
            raise ValueError("数据库密码未设置")
        
        return pymysql.connect(
            host=DB_HOST,
            user=DB_USER,
            password=DB_PASSWORD,
            database=DB_NAME,
            charset='utf8mb4',
            autocommit=False,
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def return_connection(self, conn):
        """归还连接到池"""
        with self.lock:
            if len(self.connections) < self.max_connections:
                self.connections.append(conn)
            else:
                conn.close()

db_pool = DatabasePool()

def get_connection():
    """获取数据库连接（兼容旧代码）"""
    return db_pool.get_connection()

# --- 缓存装饰器 ---
def cache_result(timeout=60):
    """简单的内存缓存装饰器"""
    def decorator(func):
        cache = {}
        
        @wraps(func)
        def wrapper(*args, **kwargs):
            key = str(args) + str(kwargs)
            now = time()
            
            if key in cache:
                result, timestamp = cache[key]
                if now - timestamp < timeout:
                    return result
            
            result = func(*args, **kwargs)
            cache[key] = (result, now)
            return result
        
        return wrapper
    return decorator

# --- 数据库备份恢复功能 ---
class DatabaseBackup:
    """数据库备份管理类"""
    
    @staticmethod
    def create_backup(description=""):
        """创建数据库备份"""
        try:
            timestamp = datetime.now(SHANGHAI_TZ).strftime('%Y%m%d_%H%M%S')
            filename = f"backup_{timestamp}.sql.gz"
            filepath = os.path.join(BACKUP_DIR, filename)
            
            dump_cmd = [
                'mysqldump',
                f'--host={DB_HOST}',
                f'--user={DB_USER}',
                f'--password={DB_PASSWORD}',
                '--skip-ssl',
                '--single-transaction',
                '--routines',
                '--triggers',
                DB_NAME
            ]
            
            process = subprocess.Popen(dump_cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            dump_data, error = process.communicate()
            
            if process.returncode != 0:
                raise Exception(f"mysqldump 失败: {error.decode('utf-8', errors='ignore')}")
            
            with gzip.open(filepath, 'wb') as f:
                f.write(dump_data)
            
            conn = get_connection()
            try:
                with conn.cursor() as cursor:
                    cursor.execute('''
                        INSERT INTO backups (filename, created_at, description, size)
                        VALUES (%s, %s, %s, %s)
                    ''', (filename, datetime.now(SHANGHAI_TZ), description, os.path.getsize(filepath)))
                    conn.commit()
            finally:
                db_pool.return_connection(conn)

            logger.info(f"数据库备份成功: {filename}")
            return True, filename
            
        except Exception as e:
            logger.error(f"数据库备份失败: {e}")
            return False, str(e)
    
    @staticmethod
    def restore_backup(filename):
        """恢复数据库备份"""
        try:
            filepath = os.path.join(BACKUP_DIR, filename)
            if not os.path.exists(filepath):
                raise Exception(f"备份文件不存在: {filename}")
            
            with gzip.open(filepath, 'rb') as f:
                dump_data = f.read()
            
            restore_cmd = [
                'mysql',
                f'--host={DB_HOST}',
                f'--user={DB_USER}',
                f'--password={DB_PASSWORD}',
                '--skip-ssl',
                DB_NAME
            ]
            
            process = subprocess.Popen(restore_cmd, stdin=subprocess.PIPE, stderr=subprocess.PIPE)
            _, error = process.communicate(input=dump_data)
            
            if process.returncode != 0:
                raise Exception(f"mysql 恢复失败: {error.decode('utf-8', errors='ignore')}")
            
            logger.info(f"数据库恢复成功: {filename}")
            return True, "数据库恢复成功"
            
        except Exception as e:
            logger.error(f"数据库恢复失败: {e}")
            return False, str(e)
    
    @staticmethod
    def restore_from_upload(file_content):
        """从上传的文件恢复数据库"""
        try:
            # 解压文件内容
            dump_data = gzip.decompress(file_content)
            
            restore_cmd = [
                'mysql',
                f'--host={DB_HOST}',
                f'--user={DB_USER}',
                f'--password={DB_PASSWORD}',
                '--skip-ssl',
                DB_NAME
            ]
            
            process = subprocess.Popen(restore_cmd, stdin=subprocess.PIPE, stderr=subprocess.PIPE)
            _, error = process.communicate(input=dump_data)
            
            if process.returncode != 0:
                raise Exception(f"mysql 恢复失败: {error.decode('utf-8', errors='ignore')}")
            
            logger.info("从上传文件恢复数据库成功")
            return True, "数据库恢复成功"
            
        except Exception as e:
            logger.error(f"从上传文件恢复数据库失败: {e}")
            return False, str(e)
    
    @staticmethod
    def list_backups():
        """列出所有备份"""
        conn = None
        try:
            conn = get_connection()
            with conn.cursor() as cursor:
                cursor.execute('SELECT filename, created_at, description, size FROM backups ORDER BY created_at DESC LIMIT 20')
                return cursor.fetchall()
        except Exception:
            backups = []
            if os.path.exists(BACKUP_DIR):
                for f in os.listdir(BACKUP_DIR):
                    if f.endswith('.sql.gz'):
                        filepath = os.path.join(BACKUP_DIR, f)
                        backups.append({
                            'filename': f,
                            'created_at': datetime.fromtimestamp(os.path.getctime(filepath)),
                            'size': os.path.getsize(filepath)
                        })
            return backups
        finally:
            if conn:
                db_pool.return_connection(conn)

# --- 数据库初始化 ---
def init_db():
    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute('''CREATE TABLE IF NOT EXISTS prices (id INT AUTO_INCREMENT PRIMARY KEY, timestamp BIGINT NOT NULL, price DOUBLE NOT NULL, date VARCHAR(16) NOT NULL, source VARCHAR(16) NOT NULL, INDEX idx_prices_date_source (date, source), INDEX idx_prices_timestamp (timestamp), INDEX idx_prices_source (source)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4''')
            cursor.execute('''CREATE TABLE IF NOT EXISTS daily_summary (date VARCHAR(16) NOT NULL, source VARCHAR(16) NOT NULL, open_price DOUBLE NOT NULL, close_price DOUBLE NOT NULL, max_price DOUBLE NOT NULL, min_price DOUBLE NOT NULL, PRIMARY KEY (date, source)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4''')
            cursor.execute('''CREATE TABLE IF NOT EXISTS config (`key` VARCHAR(32) PRIMARY KEY, `value` VARCHAR(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4''')
            cursor.execute('''CREATE TABLE IF NOT EXISTS backups (id INT AUTO_INCREMENT PRIMARY KEY, filename VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL, description TEXT, size BIGINT, INDEX idx_created_at (created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4''')
            cursor.execute("INSERT IGNORE INTO config(`key`, `value`) VALUES('interval', %s)", (str(DEFAULT_INTERVAL),))
            cursor.execute("INSERT IGNORE INTO config(`key`, `value`) VALUES('auto_backup_enabled', 'false')")
            cursor.execute("INSERT IGNORE INTO config(`key`, `value`) VALUES('auto_backup_days', '7')")
            conn.commit()
        logger.info('数据库初始化完成')
    except Exception as e:
        logger.error(f'数据库初始化失败: {e}')
        raise
    finally:
        if conn:
            db_pool.return_connection(conn)

# --- 数据采集与处理 ---
def calculate_date_in_shanghai_tz(timestamp_seconds):
    """计算上海时区日期"""
    return datetime.fromtimestamp(timestamp_seconds, SHANGHAI_TZ).strftime('%Y-%m-%d')

def prune_old_records():
    conn = None
    try:
        conn = get_connection()
        cutoff_date = (datetime.now(SHANGHAI_TZ) - timedelta(days=MAX_RECORD_DAYS)).strftime('%Y-%m-%d')
        with conn.cursor() as cursor:
            cursor.execute('DELETE FROM prices WHERE date < %s', (cutoff_date,))
            deleted_prices = cursor.rowcount
            cursor.execute('DELETE FROM daily_summary WHERE date < %s', (cutoff_date,))
            deleted_summary = cursor.rowcount
            conn.commit()
        if deleted_prices > 0 or deleted_summary > 0:
            logger.info(f'已清理旧数据: 价格记录{deleted_prices}条, 汇总记录{deleted_summary}条')
    except Exception as e:
        logger.error(f"清理旧数据失败: {e}")
    finally:
        if conn:
            db_pool.return_connection(conn)

def generate_daily_summary():
    target_date = (datetime.now(SHANGHAI_TZ) - timedelta(days=1)).strftime('%Y-%m-%d')
    for source_key, source_config in SOURCES_CONFIG.items():
        conn = None
        try:
            conn = get_connection()
            with conn.cursor() as cursor:
                cursor.execute("SELECT 1 FROM daily_summary WHERE date=%s AND source=%s", (target_date, source_key))
                if cursor.fetchone(): continue
                cursor.execute('''SELECT MAX(price) as max_p, MIN(price) as min_p, (SELECT price FROM prices WHERE date=%s AND source=%s ORDER BY timestamp ASC LIMIT 1) as open_p, (SELECT price FROM prices WHERE date=%s AND source=%s ORDER BY timestamp DESC LIMIT 1) as close_p FROM prices WHERE date=%s AND source=%s''', (target_date, source_key, target_date, source_key, target_date, source_key))
                row = cursor.fetchone()
                if row and row['max_p'] is not None:
                    cursor.execute('''INSERT INTO daily_summary (date, source, open_price, close_price, max_price, min_price) VALUES (%s, %s, %s, %s, %s, %s)''', (target_date, source_key, row['open_p'] or row['min_p'], row['close_p'] or row['max_p'], row['max_p'], row['min_p']))
                    conn.commit()
                    logger.info(f"已生成 {source_config['name']} {target_date} 汇总")
        except Exception as e:
            logger.error(f"生成 {source_config['name']} 汇总失败: {e}")
        finally:
            if conn:
                db_pool.return_connection(conn)

def _get_value_from_path(data_dict, path_list):
    """从嵌套字典提取值"""
    current = data_dict
    for key in path_list:
        if isinstance(current, dict) and key in current:
            current = current[key]
        else: return None
    return current

def fetch_and_store():
    """采集并存储价格数据"""
    headers = {"User-Agent": "Mozilla/5.0"}
    for source_key, source_config in SOURCES_CONFIG.items():
        try:
            resp = requests.get(source_config['api_url'], timeout=10, headers=headers)
            resp.raise_for_status()
            data = resp.json()
            price_str = _get_value_from_path(data, source_config['path_to_price'])
            time_ms_str = _get_value_from_path(data, source_config['path_to_time'])
            if price_str and time_ms_str:
                price = float(price_str)
                timestamp = int(time_ms_str) // 1000
                date_str = calculate_date_in_shanghai_tz(timestamp)
                conn = get_connection()
                try:
                    with conn.cursor() as cursor:
                        cursor.execute('INSERT INTO prices(timestamp, price, date, source) VALUES(%s,%s,%s,%s)', (timestamp, price, date_str, source_key))
                        conn.commit()
                finally:
                    db_pool.return_connection(conn)
                logger.info(f"{source_config['name']}: 价格{price} 日期{date_str}")
        except Exception as e:
            logger.error(f"{source_config['name']} 采集失败: {e}")

# --- 调度器管理 ---
def get_interval():
    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute("SELECT value FROM config WHERE `key`='interval'")
            row = cursor.fetchone()
            return int(row['value']) if row else DEFAULT_INTERVAL
    except Exception as e:
        logger.error(f"获取间隔失败: {e}")
        return DEFAULT_INTERVAL
    finally:
        if conn:
            db_pool.return_connection(conn)

def set_interval_in_db(new_interval):
    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute("UPDATE config SET value=%s WHERE `key`='interval'", (str(new_interval),))
            conn.commit()
        return True
    except Exception as e:
        logger.error(f"设置间隔失败: {e}")
        return False
    finally:
        if conn:
            db_pool.return_connection(conn)

def reschedule_price_job(new_interval):
    global scheduler
    if scheduler and scheduler.running:
        try:
            scheduler.reschedule_job('price_job', trigger='interval', seconds=new_interval)
            return True, "任务已重新调度"
        except Exception as e:
            return False, f"调度失败: {e}"
    return False, "调度器未运行"

# --- 自动备份配置管理 ---
def get_auto_backup_config():
    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute("SELECT `key`, `value` FROM config WHERE `key` IN ('auto_backup_enabled', 'auto_backup_days')")
            rows = cursor.fetchall()
            config = {'auto_backup_enabled': False, 'auto_backup_days': 7}
            for row in rows:
                if row['key'] == 'auto_backup_enabled':
                    config['auto_backup_enabled'] = row['value'].lower() == 'true'
                elif row['key'] == 'auto_backup_days':
                    config['auto_backup_days'] = int(row['value'])
            return config
    except Exception as e:
        logger.error(f"获取自动备份配置失败: {e}")
        return {'auto_backup_enabled': False, 'auto_backup_days': 7}
    finally:
        if conn:
            db_pool.return_connection(conn)

def set_auto_backup_config(enabled, days):
    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute("UPDATE config SET value=%s WHERE `key`='auto_backup_enabled'", ('true' if enabled else 'false',))
            cursor.execute("UPDATE config SET value=%s WHERE `key`='auto_backup_days'", (str(days),))
            conn.commit()
        return True
    except Exception as e:
        logger.error(f"设置自动备份配置失败: {e}")
        return False
    finally:
        if conn:
            db_pool.return_connection(conn)

def auto_backup_task():
    """自动备份任务"""
    config = get_auto_backup_config()
    if config['auto_backup_enabled']:
        DatabaseBackup.create_backup("自动备份")

jobstores = {'default': SQLAlchemyJobStore(url=f'sqlite:///{JOBSTORE_DB}')}
scheduler = BackgroundScheduler(jobstores=jobstores, timezone='Asia/Shanghai')

def start_scheduler():
    """启动调度器"""
    current_interval = get_interval()
    scheduler.add_job(fetch_and_store, 'interval', seconds=current_interval, id='price_job', replace_existing=True)
    scheduler.add_job(generate_daily_summary, 'cron', hour=0, minute=1, id='daily_summary_job', replace_existing=True)
    scheduler.add_job(prune_old_records, 'cron', hour=1, minute=0, id='prune_records_job', replace_existing=True)
    scheduler.add_job(auto_backup_task, 'cron', hour=2, minute=0, id='auto_backup_job', replace_existing=True)
    scheduler.start()
    logger.info(f"调度器已启动，采集间隔: {current_interval}秒")

# --- 优化的前端模板 ---
TEMPLATE = '''
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
<title>黄金价格监控系统</title>
<link rel="stylesheet" href="/static/css/style.css">
<script src="/static/js/luxon.min.js"></script>
<script src="/static/js/chart.umd.min.js"></script>
<script src="/static/js/chartjs-adapter-luxon.umd.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/hammerjs@2.0.8/hammer.min.js"></script>
<script src="/static/js/chartjs-plugin-zoom.min.js"></script>
</head>
<body>
<div class="container">
    <div class="header">
        <h1>⚜️ 黄金价格实时监控系统</h1>
        <p style="margin-top: 10px; opacity: 0.9;">智能化数据采集 · 可视化分析 · 自动备份恢复</p>
    </div>
    <div class="nav-tabs">
        <button class="nav-tab active" data-tab="realtime">实时监控</button>
        <button class="nav-tab" data-tab="history">历史数据</button>
        <button class="nav-tab" data-tab="analysis">数据分析</button>
        <button class="nav-tab" data-tab="settings">系统设置</button>
    </div>
    <div class="tab-content">
        <div class="tab-pane active" id="realtime">
            <div class="date-display" id="current-date">当前日期: --</div>
            <div class="source-selector">
                <span style="font-weight: 600;">数据源：</span>
                {% for key, info in sources.items() %}<button class="source-btn {% if loop.first %}active{% endif %}" data-source="{{ key }}">{{ info.name }}</button>{% endfor %}
            </div>
            <div class="price-display" id="latest-price"><div>最新金价：<span style="font-size: 1.5em;">--</span> 元/克</div><div style="font-size: 0.8em; margin-top: 10px; opacity: 0.9;">更新时间：--</div></div>
            <div class="extremes-display">
                <div class="extreme-card"><div>今日最高</div><div class="extreme-value extreme-high" id="today-high">--</div></div>
                <div class="extreme-card"><div>今日最低</div><div class="extreme-value extreme-low" id="today-low">--</div></div>
            </div>
            <div class="chart-container">
                <button class="chart-reset-btn" onclick="resetRealtimeChart()">重置视图</button>
                <div class="chart-hint">💡 提示：鼠标滚轮缩放，拖动平移，流畅查看数据</div>
                <canvas id="realtimeChart"></canvas>
            </div>
        </div>
        <div class="tab-pane" id="history">
            <div class="date-range-selector">
                <label>日期范围：</label>
                <input type="date" id="startDate" class="date-input">
                <span>至</span>
                <input type="date" id="endDate" class="date-input">
                <button class="btn btn-primary" onclick="loadHistoryByDateRange()">查询</button>
                <button class="btn btn-secondary" onclick="loadHistoryDefaultMonth()">最近一月</button>
            </div>
            <div class="source-selector">
                <span style="font-weight: 600;">数据源：</span>
                {% for key, info in sources.items() %}<button class="source-btn {% if loop.first %}active{% endif %}" data-source="{{ key }}">{{ info.name }}</button>{% endfor %}
            </div>
            <div class="chart-container">
                <button class="chart-reset-btn" onclick="resetHistoryChart()">重置视图</button>
                <div class="chart-hint">💡 提示：鼠标滚轮缩放，拖动平移，流畅查看数据</div>
                <canvas id="historyChart"></canvas>
            </div>
            <div class="data-card">
                <h5>每日价格汇总</h5>
                <table><thead><tr><th>日期</th><th>开盘价</th><th>收盘价</th><th>最高价</th><th>最低价</th><th>涨跌幅</th></tr></thead><tbody id="historyTableBody"></tbody></table>
                <div class="pagination" id="pagination"></div>
            </div>
        </div>
        <div class="tab-pane" id="analysis">
            <div class="data-card">
                <h5>价格趋势分析</h5>
                <div class="chart-container">
                    <button class="chart-reset-btn" onclick="resetTrendChart()">重置视图</button>
                    <div class="chart-hint">💡 提示：鼠标滚轮缩放，拖动平移，流畅查看数据</div>
                    <canvas id="trendChart"></canvas>
                </div>
            </div>
            <div class="data-card">
                <h5>统计信息</h5>
                <div id="statistics" style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px;"></div>
            </div>
        </div>
        <div class="tab-pane" id="settings">
            <div class="data-card">
                <h5>采集设置</h5>
                <div class="form-group"><label class="form-label">采集间隔（秒）</label><div style="display: flex; gap: 10px;"><input type="number" id="intervalInput" class="form-control" min="3" style="max-width: 200px;"><button class="btn btn-primary" onclick="saveInterval()">保存设置</button></div></div><div id="intervalMsg"></div>
            </div>
            <div class="data-card">
                <h5>数据库备份</h5>
                <div class="form-group"><label class="form-label">自动备份设置</label><div style="display: flex; align-items: center; gap: 20px; margin: 10px 0;"><label><input type="checkbox" id="autoBackupEnabled"> 启用自动备份</label><div><span>每</span><input type="number" id="autoBackupDays" min="1" max="365" style="width: 60px; padding: 5px;" value="7"><span>天执行一次</span></div><button class="btn btn-primary" onclick="saveAutoBackupConfig()">保存</button></div></div>
                <div style="margin: 20px 0;"><button class="btn btn-success" onclick="createBackup()">创建备份</button><button class="btn btn-primary" onclick="showBackups()">查看备份</button></div>
                <div id="backupProgress" style="display:none;margin:20px 0;"><div class="progress-bar"><div class="progress-fill" id="progressFill"></div></div><div class="progress-text" id="progressText">正在备份...</div></div>
                <div id="backupList"></div>
            </div>
            <div class="data-card">
                <h5>数据库还原</h5>
                <div class="form-group">
                    <label class="form-label">选择备份文件进行还原</label>
                    <input type="file" id="restoreFile" accept=".gz,.sql.gz" class="form-control" style="margin: 10px 0;">
                    <button class="btn btn-warning" onclick="restoreFromFile()">上传并还原</button>
                </div>
                <div id="restoreProgress" style="display:none;margin:20px 0;">
                    <div class="progress-bar"><div class="progress-fill" id="restoreProgressFill"></div></div>
                    <div class="progress-text" id="restoreProgressText">正在还原...</div>
                </div>
            </div>
            <div class="data-card">
                <h5>系统信息</h5>
                <div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); gap: 15px; margin-top: 20px;">
                    <div><span style="color: #666;">版本号：</span><span>V2.3.0</span></div>
                    <div><span style="color: #666;">数据保留天数：</span><span>{{ MAX_RECORD_DAYS }} 天</span></div>
                    <div><span style="color: #666;">当前数据源数量：</span><span>{{ sources|length }} 个</span></div>
                </div>
            </div>
        </div>
    </div>
    <div class="footer"><p>© 2024 黄金价格监控系统 · Powered by yishiliu</p></div>
</div>

<script>
// 检查依赖并注册缩放插件
console.log('=== 插件加载状态 ===');
console.log('Chart.js:', typeof Chart !== 'undefined' ? Chart.version : '✗ 未加载');
console.log('Hammer.js:', typeof Hammer !== 'undefined' ? '✓ 已加载' : '✗ 未加载');
console.log('ChartZoom:', typeof ChartZoom !== 'undefined' ? '✓ 已加载' : '✗ 未加载');

// 注册插件
if (typeof Chart !== 'undefined' && typeof ChartZoom !== 'undefined') {
    try {
        Chart.register(ChartZoom);
        console.log('✓ ChartZoom插件注册成功');
        
        // 验证插件是否真的被注册
        setTimeout(function() {
            const zoomPlugin = Chart.registry.plugins.get('zoom');
            console.log('Zoom插件验证:', zoomPlugin ? '✓ 已注册' : '✗ 未注册');
        }, 100);
    } catch(e) {
        console.error('✗ 插件注册失败:', e);
    }
} else {
    console.error('✗ 缺少必要的库');
    if (typeof Chart === 'undefined') console.error('  - Chart.js未加载');
    if (typeof ChartZoom === 'undefined') console.error('  - ChartZoom未加载');
}

let currentSource = '{{ sources.keys()|list|first }}';
let realtimeChart, historyChart, trendChart, realtimeTimer;
let currentViewDate = new Date().toISOString().split('T')[0];

const chartZoomOptions = {
    plugins: {
        legend: { display: false },
        zoom: {
            pan: {
                enabled: true,
                mode: 'x',
                threshold: 5
            },
            zoom: {
                wheel: {
                    enabled: true,
                    speed: 0.15
                },
                pinch: {
                    enabled: true
                },
                mode: 'x'
            },
            limits: {
                x: { minRange: 60000 }
            }
        },
        tooltip: {
            enabled: true,
            mode: 'index',
            intersect: false,
            animation: {
                duration: 0
            }
        }
    },
    interaction: {
        intersect: false,
        mode: 'index'
    },
    animation: {
        duration: 0
    },
    responsiveAnimationDuration: 0
};

document.addEventListener('DOMContentLoaded', () => {
    setupEventListeners();
    loadRealtimeData();
    loadHistoryDefaultMonth();
    loadInterval();
    loadAutoBackupConfig();
    startRealtimeUpdate();
    updateCurrentDateDisplay();
});

function setupEventListeners() {
    document.querySelectorAll('.nav-tab').forEach(tab => tab.addEventListener('click', function() {
        document.querySelectorAll('.nav-tab, .tab-pane').forEach(el => el.classList.remove('active'));
        this.classList.add('active');
        document.getElementById(this.dataset.tab).classList.add('active');
        if (this.dataset.tab === 'history') loadHistoryDefaultMonth();
        else if (this.dataset.tab === 'analysis') loadAnalysisData();
    }));
    document.querySelectorAll('.source-btn').forEach(btn => btn.addEventListener('click', function() {
        const newSource = this.dataset.source;
        if (newSource === currentSource) return;
        currentSource = newSource;
        document.querySelectorAll('.source-btn').forEach(b => b.classList.toggle('active', b.dataset.source === currentSource));
        const activeTab = document.querySelector('.tab-pane.active').id;
        if (activeTab === 'realtime') loadRealtimeData();
        else if (activeTab === 'history') loadHistoryByDateRange();
        else if (activeTab === 'analysis') loadAnalysisData();
    }));
}

function updateCurrentDateDisplay(date = null) {
    const displayDate = date || currentViewDate;
    document.getElementById('current-date').textContent = '当前日期: ' + displayDate;
}

async function fetchAPI(url, options) {
    try {
        const response = await fetch(url, options);
        if (!response.ok) throw new Error(`HTTP error! status: ${response.status}`);
        return await response.json();
    } catch (error) {
        console.error('API Error:', error);
        return null;
    }
}

async function loadRealtimeData(date = null) {
    // 保存当前滚动位置
    const scrollY = window.pageYOffset;
    const scrollX = window.pageXOffset;
    
    const targetDate = date || new Date().toISOString().split('T')[0];
    currentViewDate = targetDate;
    updateCurrentDateDisplay(targetDate);
    
    const [dailyData, extremesData] = await Promise.all([
        fetchAPI(`/api/daily?date=${targetDate}&source=${currentSource}`),
        fetchAPI(`/api/today_extremes?date=${targetDate}&source=${currentSource}`)
    ]);
    
    if (dailyData && dailyData.length > 0) {
        updateLatestPrice(dailyData[dailyData.length - 1]);
        renderRealtimeChart(dailyData);
    } else {
        document.getElementById('latest-price').innerHTML = `<div>金价: <span style="font-size: 1.5em;">--</span></div><div>${targetDate} 无数据</div>`;
        if (realtimeChart) { 
            realtimeChart.data.datasets[0].data = []; 
            realtimeChart.update(); 
        }
    }
    
    if (extremesData) {
        const newHigh = extremesData.max_price ? extremesData.max_price.toFixed(2) : '--';
        const newLow = extremesData.min_price ? extremesData.min_price.toFixed(2) : '--';
        
        // 只在值真正变化时才更新DOM，避免不必要的重绘
        if (lastDisplayedHigh !== newHigh) {
            document.getElementById('today-high').textContent = newHigh;
            lastDisplayedHigh = newHigh;
        }
        if (lastDisplayedLow !== newLow) {
            document.getElementById('today-low').textContent = newLow;
            lastDisplayedLow = newLow;
        }
    }
    
    // 使用requestAnimationFrame在下一帧恢复滚动位置，确保渲染完成
    requestAnimationFrame(() => {
        if (window.pageYOffset !== scrollY || window.pageXOffset !== scrollX) {
            window.scrollTo(scrollX, scrollY);
        }
    });
}

let lastDisplayedPrice = null;
let lastDisplayedTime = null;
let lastDisplayedHigh = null;
let lastDisplayedLow = null;

function updateLatestPrice(latest) {
    const newPrice = latest.price.toFixed(2);
    const newTime = new Date(latest.timestamp * 1000).toLocaleTimeString('zh-CN');
    
    // 只在价格或时间真正变化时才更新DOM，避免不必要的重绘
    if (lastDisplayedPrice !== newPrice || lastDisplayedTime !== newTime) {
        document.getElementById('latest-price').innerHTML = `<div>最新金价：<span style="font-size: 1.5em;">${newPrice}</span> 元/克</div><div style="font-size: 0.8em; margin-top: 10px; opacity: 0.9;">更新时间：${newTime}</div>`;
        lastDisplayedPrice = newPrice;
        lastDisplayedTime = newTime;
    }
}

function renderRealtimeChart(data) {
    const chartData = data.map(d => ({ x: d.timestamp * 1000, y: d.price }));
    if (realtimeChart) {
        // 增量更新策略：智能比较并只更新变化的数据
        const currentData = realtimeChart.data.datasets[0].data;
        let needsUpdate = false;
        
        // 如果数据长度不同，说明有新数据
        if (currentData.length !== chartData.length) {
            needsUpdate = true;
        } else {
            // 检查最后几个数据点是否有变化（通常只有最新的数据会变化）
            const checkCount = Math.min(5, chartData.length);
            for (let i = chartData.length - checkCount; i < chartData.length; i++) {
                if (!currentData[i] || 
                    currentData[i].x !== chartData[i].x || 
                    currentData[i].y !== chartData[i].y) {
                    needsUpdate = true;
                    break;
                }
            }
        }
        
        // 只在真正有变化时才更新，update('none')不会触发滚动
        if (needsUpdate) {
            realtimeChart.data.datasets[0].data = chartData;
            realtimeChart.update('none');
        }
    } else {
        const ctx = document.getElementById('realtimeChart').getContext('2d');
        realtimeChart = new Chart(ctx, {
            type: 'line',
            data: {
                datasets: [{
                    label: '实时价格',
                    data: chartData,
                    borderColor: '#667eea',
                    backgroundColor: 'rgba(102,126,234,0.1)',
                    tension: 0.4,
                    pointRadius: 0,
                    borderWidth: 1.5,
                    fill: true
                }]
            },
            options: {
                ...chartZoomOptions,
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    x: {
                        type: 'time',
                        time: {
                            tooltipFormat: 'HH:mm:ss',
                            displayFormats: {
                                hour: 'HH:mm',
                                minute: 'HH:mm'
                            }
                        },
                        ticks: {
                            autoSkip: true,
                            maxTicksLimit: 10,
                            maxRotation: 0,
                            minRotation: 0
                        }
                    },
                    y: {
                        title: {
                            display: true,
                            text: '价格(元/克)'
                        },
                        ticks: {
                            callback: function(value) {
                                return value.toFixed(2);
                            }
                        }
                    }
                }
            }
        });
    }
}

function resetRealtimeChart() { if (realtimeChart) realtimeChart.resetZoom(); }

async function loadHistoryDefaultMonth() {
    const endDate = new Date();
    const startDate = new Date();
    startDate.setMonth(startDate.getMonth() - 1);
    
    document.getElementById('startDate').value = startDate.toISOString().split('T')[0];
    document.getElementById('endDate').value = endDate.toISOString().split('T')[0];
    
    await loadHistoryByDateRange();
}

async function loadHistoryByDateRange() {
    const startDate = document.getElementById('startDate')?.value;
    const endDate = document.getElementById('endDate')?.value;
    
    let url = `/api/summary_range?source=${currentSource}`;
    if (startDate) url += `&start_date=${startDate}`;
    if (endDate) url += `&end_date=${endDate}`;
    
    const result = await fetchAPI(url);
    const tbody = document.getElementById('historyTableBody');
    if (result && result.items && result.items.length > 0) {
        renderHistoryChart(result.items);
        renderHistoryTable(result.items, tbody);
    } else {
        tbody.innerHTML = '<tr><td colspan="6" style="text-align:center;">暂无数据</td></tr>';
    }
}

function renderHistoryChart(data) {
    if (historyChart) historyChart.destroy();
    const chartData = [...data].reverse();
    const ctx = document.getElementById('historyChart').getContext('2d');
    historyChart = new Chart(ctx, {
        type: 'line',
        data: {
            labels: chartData.map(d => d.date),
            datasets: [
                {
                    label: '最高价',
                    data: chartData.map(d => d.max_price),
                    borderColor: '#dc3545',
                    backgroundColor: 'rgba(220,53,69,0.1)',
                    tension: 0.2,
                    pointRadius: 3,
                    pointHoverRadius: 5,
                    borderWidth: 2,
                    fill: true
                },
                {
                    label: '最低价',
                    data: chartData.map(d => d.min_price),
                    borderColor: '#28a745',
                    backgroundColor: 'rgba(40,167,69,0.1)',
                    tension: 0.2,
                    pointRadius: 3,
                    pointHoverRadius: 5,
                    borderWidth: 2,
                    fill: true
                }
            ]
        },
        options: {
            ...chartZoomOptions,
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                ...chartZoomOptions.plugins,
                legend: {
                    display: true,
                    position: 'top'
                }
            },
            scales: {
                y: {
                    title: {
                        display: true,
                        text: '价格(元/克)'
                    },
                    ticks: {
                        callback: function(value) {
                            return value.toFixed(2);
                        }
                    }
                }
            }
        }
    });
}

function resetHistoryChart() { if (historyChart) historyChart.resetZoom(); }

function renderHistoryTable(data, tbody) {
    tbody.innerHTML = data.map(item => {
        const change = ((item.close_price - item.open_price) / item.open_price * 100);
        const changeColor = change >= 0 ? '#dc3545' : '#28a745';
        const changeSymbol = change >= 0 ? '↑' : '↓';
        return `<tr data-date="${item.date}" class="clickable-row">
            <td>${item.date}</td>
            <td class="clickable-value" data-date="${item.date}">${item.open_price.toFixed(2)}</td>
            <td class="clickable-value" data-date="${item.date}">${item.close_price.toFixed(2)}</td>
            <td class="clickable-value" data-date="${item.date}">${item.max_price.toFixed(2)}</td>
            <td class="clickable-value" data-date="${item.date}">${item.min_price.toFixed(2)}</td>
            <td style="color: ${changeColor};">${changeSymbol} ${Math.abs(change).toFixed(2)}%</td>
        </tr>`;
    }).join('');
    
    tbody.querySelectorAll('.clickable-value').forEach(cell => {
        cell.addEventListener('click', function(e) {
            e.stopPropagation();
            const date = this.dataset.date;
            if (date) {
                document.querySelector('.nav-tab[data-tab="realtime"]').click();
                loadRealtimeData(date);
            }
        });
    });
}

async function loadAnalysisData() {
    const result = await fetchAPI(`/api/summary_range?source=${currentSource}&limit=30`);
    if (result && result.items && result.items.length > 0) {
        renderTrendChart(result.items);
        updateStatistics(result.items);
    }
}

function renderTrendChart(data) {
    if (trendChart) trendChart.destroy();
    const chartData = [...data].reverse();
    const avgPrices = chartData.map(d => (d.max_price + d.min_price) / 2);
    const ctx = document.getElementById('trendChart').getContext('2d');
    trendChart = new Chart(ctx, {
        type: 'line',
        data: {
            labels: chartData.map(d => d.date),
            datasets: [{
                label: '日均价',
                data: avgPrices,
                borderColor: '#667eea',
                backgroundColor: 'rgba(102,126,234,0.1)',
                tension: 0.4,
                pointRadius: 3,
                pointHoverRadius: 6,
                borderWidth: 2,
                fill: true
            }]
        },
        options: {
            ...chartZoomOptions,
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                ...chartZoomOptions.plugins,
                legend: {
                    display: true,
                    position: 'top'
                }
            },
            scales: {
                y: {
                    title: {
                        display: true,
                        text: '价格(元/克)'
                    },
                    ticks: {
                        callback: function(value) {
                            return value.toFixed(2);
                        }
                    }
                }
            }
        }
    });
}

function resetTrendChart() { if (trendChart) trendChart.resetZoom(); }

function updateStatistics(data) {
    const today = data[0];
    const week = data.slice(0, 7);
    const month = data;
    const weekAvg = week.reduce((s, d) => s + (d.max_price + d.min_price) / 2, 0) / week.length;
    const monthAvg = month.reduce((s, d) => s + (d.max_price + d.min_price) / 2, 0) / month.length;
    const createCard = (label, value, color) => `<div style="text-align: center; padding: 20px; background: #f8f9fa; border-radius: 10px;"><div style="color: #666;">${label}</div><div style="font-size: 1.5em; color: ${color}; margin-top: 10px;">${value}</div></div>`;
    document.getElementById('statistics').innerHTML = createCard('今日最高', today.max_price.toFixed(2), '#dc3545') + createCard('今日最低', today.min_price.toFixed(2), '#28a745') + createCard('7日均价', weekAvg.toFixed(2), '#007bff') + createCard('30日均价', monthAvg.toFixed(2), '#6610f2');
}

async function saveInterval() {
    const interval = document.getElementById('intervalInput').value;
    if (!interval || interval < 3) return showMessage('intervalMsg', '采集间隔不能小于3秒', 'danger');
    const result = await fetchAPI('/api/interval', { method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify({interval: parseInt(interval)}) });
    if (result) showMessage('intervalMsg', result.msg || result.error, result.success ? 'success' : 'danger');
}

async function loadInterval() {
    const data = await fetchAPI('/api/interval');
    if (data) document.getElementById('intervalInput').value = data.interval;
}

async function loadAutoBackupConfig() {
    const config = await fetchAPI('/api/auto_backup_config');
    if (config) {
        document.getElementById('autoBackupEnabled').checked = config.auto_backup_enabled;
        document.getElementById('autoBackupDays').value = config.auto_backup_days;
    }
}

async function saveAutoBackupConfig() {
    const enabled = document.getElementById('autoBackupEnabled').checked;
    const days = parseInt(document.getElementById('autoBackupDays').value);
    if (days < 1 || days > 365) return alert('自动备份周期必须在1-365天之间');
    const result = await fetchAPI('/api/auto_backup_config', { method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify({enabled, days}) });
    if (result) alert(result.success ? '保存成功' : '保存失败: ' + result.error);
}

async function createBackup() {
    const description = prompt('请输入备份描述（可选）：', '');
    if (description === null) return;
    
    const progressDiv = document.getElementById('backupProgress');
    const progressFill = document.getElementById('progressFill');
    const progressText = document.getElementById('progressText');
    
    progressDiv.style.display = 'block';
    progressFill.style.width = '0%';
    progressText.textContent = '正在备份...';
    
    // 模拟进度
    let progress = 0;
    const interval = setInterval(() => {
        progress += 10;
        progressFill.style.width = progress + '%';
        if (progress >= 90) clearInterval(interval);
    }, 100);
    
    const result = await fetchAPI('/api/backup', { method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify({description}) });
    
    clearInterval(interval);
    progressFill.style.width = '100%';
    
    if (result) {
        progressText.textContent = result.success ? '备份完成！' : '备份失败！';
        alert(result.success ? `备份成功: ${result.filename}` : `备份失败: ${result.error}`);
        if(result.success) showBackups();
    }
    
    setTimeout(() => {
        progressDiv.style.display = 'none';
    }, 2000);
}

async function showBackups() {
    const backups = await fetchAPI('/api/backup');
    const listEl = document.getElementById('backupList');
    if (!backups) return listEl.innerHTML = '<p style="text-align:center; color:#999;">加载备份列表失败</p>';
    listEl.innerHTML = backups.length ? backups.map(b => `<div class="backup-item"><div class="backup-info"><div class="backup-name">${b.filename}</div><div class="backup-meta">时间: ${new Date(b.created_at).toLocaleString('zh-CN')} | 大小: ${(b.size / 1024).toFixed(2)} KB ${b.description ? `| ${b.description}` : ''}</div></div><div class="backup-actions"><button class="btn btn-primary btn-sm" onclick="restoreBackup('${b.filename}')">恢复</button><button class="btn btn-danger btn-sm" onclick="deleteBackup('${b.filename}')">删除</button></div></div>`).join('') : '<p style="text-align:center; color:#666;">暂无备份</p>';
}

async function restoreBackup(filename) {
    if (!confirm(`确认要恢复备份 ${filename} 吗？当前数据将被覆盖！`)) return;
    const result = await fetchAPI('/api/restore', { method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify({filename}) });
    if (result) { 
        alert(result.success ? '恢复成功！' : `恢复失败: ${result.error}`); 
        if(result.success) location.reload(); 
    }
}

async function restoreFromFile() {
    const fileInput = document.getElementById('restoreFile');
    const file = fileInput.files[0];
    
    if (!file) {
        alert('请先选择备份文件');
        return;
    }
    
    if (!file.name.endsWith('.gz')) {
        alert('请选择有效的备份文件（.gz格式）');
        return;
    }
    
    if (!confirm(`确认要从文件 ${file.name} 恢复数据库吗？当前数据将被覆盖！`)) return;
    
    const progressDiv = document.getElementById('restoreProgress');
    const progressFill = document.getElementById('restoreProgressFill');
    const progressText = document.getElementById('restoreProgressText');
    
    progressDiv.style.display = 'block';
    progressFill.style.width = '0%';
    progressText.textContent = '正在读取文件...';
    
    const formData = new FormData();
    formData.append('file', file);
    
    // 模拟进度
    let progress = 0;
    const interval = setInterval(() => {
        progress += 5;
        progressFill.style.width = progress + '%';
        if (progress >= 90) clearInterval(interval);
    }, 100);
    
    try {
        const response = await fetch('/api/restore_upload', {
            method: 'POST',
            body: formData
        });
        
        clearInterval(interval);
        progressFill.style.width = '100%';
        
        const result = await response.json();
        
        if (result.success) {
            progressText.textContent = '还原完成！';
            alert('数据库还原成功！');
            setTimeout(() => location.reload(), 1500);
        } else {
            progressText.textContent = '还原失败！';
            alert(`还原失败: ${result.error}`);
        }
    } catch (error) {
        clearInterval(interval);
        progressText.textContent = '还原失败！';
        alert(`还原失败: ${error.message}`);
    }
    
    setTimeout(() => {
        progressDiv.style.display = 'none';
        fileInput.value = '';
    }, 3000);
}

async function deleteBackup(filename) {
    if (!confirm(`确认要删除备份 ${filename} 吗？`)) return;
    const result = await fetchAPI(`/api/backup/${filename}`, { method: 'DELETE' });
    if (result) { 
        alert(result.success ? '删除成功！' : `删除失败: ${result.error}`); 
        if(result.success) showBackups(); 
    }
}

let lastUpdateTime = 0;
const UPDATE_INTERVAL = 3000;

function startRealtimeUpdate() {
    if (realtimeTimer) clearInterval(realtimeTimer);
    realtimeTimer = setInterval(() => {
        const now = Date.now();
        const activeTab = document.querySelector('.tab-pane.active')?.id;

        // 只在实时监控标签页且查看今日数据时更新
        if (activeTab === 'realtime' && currentViewDate === new Date().toISOString().split('T')[0]) {
            // 防抖：确保不会频繁更新
            if (now - lastUpdateTime >= UPDATE_INTERVAL) {
                lastUpdateTime = now;
                loadRealtimeData();
            }
        }
    }, UPDATE_INTERVAL);
}

function showMessage(id, msg, type) {
    const el = document.getElementById(id);
    el.innerHTML = `<div class="alert alert-${type}">${msg}</div>`;
    setTimeout(() => el.innerHTML = '', 5000);
}
</script>
</body>
</html>
'''

# --- Flask路由 ---
@app.route('/')
def index(): 
    return render_template_string(TEMPLATE, sources=SOURCES_CONFIG, MAX_RECORD_DAYS=MAX_RECORD_DAYS, HISTORY_ITEMS_PER_PAGE=HISTORY_ITEMS_PER_PAGE)

@app.route('/static/<path:filename>')
def serve_static(filename):
    """提供静态文件服务"""
    return send_from_directory(STATIC_DIR, filename)

@app.route('/api/daily')
def api_daily():
    conn = None
    try:
        # 参数校验
        query_date = request.args.get('date')
        source = request.args.get('source')
        if not query_date or not source:
            return jsonify({'error': '缺少必需参数: date 或 source'}), 400
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute('SELECT `timestamp`, `price` FROM `prices` WHERE `date`=%s AND `source`=%s ORDER BY `timestamp` ASC', 
                          (query_date, source))
            return jsonify(cursor.fetchall())
    finally:
        if conn: db_pool.return_connection(conn)

@app.route('/api/summary_range')
def api_summary_range():
    """获取指定日期范围的汇总数据"""
    source = request.args.get('source')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    limit = request.args.get('limit', 30, type=int)
    
    conn = None
    try:
        # 参数校验
        if not source:
            return jsonify({'error': '缺少必需参数: source'}), 400
        if start_date and end_date and start_date > end_date:
            # 交换错误的日期顺序
            start_date, end_date = end_date, start_date
        conn = get_connection()
        with conn.cursor() as cursor:
            if start_date and end_date:
                cursor.execute('''
                    SELECT `date`, `open_price`, `close_price`, `max_price`, `min_price`
                    FROM `daily_summary`
                    WHERE `source`=%s AND `date`>=%s AND `date`<=%s
                    ORDER BY `date` DESC
                ''', (source, start_date, end_date))
            else:
                cursor.execute('''
                    SELECT `date`, `open_price`, `close_price`, `max_price`, `min_price`
                    FROM `daily_summary`
                    WHERE `source`=%s
                    ORDER BY `date` DESC
                    LIMIT %s
                ''', (source, int(limit)))
            
            items = cursor.fetchall()
            return jsonify({'items': items})
    finally:
        if conn: db_pool.return_connection(conn)

@app.route('/api/interval', methods=['GET', 'POST'])
def api_interval():
    if request.method == 'GET': 
        return jsonify({'interval': get_interval()})
    data = request.get_json()
    new_interval = int(data.get('interval', DEFAULT_INTERVAL))
    if new_interval < 3: 
        return jsonify({'success': False, 'msg': '间隔不能小于3秒'}), 400
    if set_interval_in_db(new_interval):
        success, msg = reschedule_price_job(new_interval)
        return jsonify({'success': True, 'msg': msg})
    return jsonify({'success': False, 'msg': '保存失败'}), 500

@app.route('/api/auto_backup_config', methods=['GET', 'POST'])
def api_auto_backup_config():
    if request.method == 'GET': 
        return jsonify(get_auto_backup_config())
    data = request.get_json()
    if set_auto_backup_config(bool(data.get('enabled')), int(data.get('days', 7))):
        return jsonify({'success': True})
    return jsonify({'success': False, 'error': '保存失败'}), 500

@app.route('/api/backup', methods=['GET', 'POST'])
def api_backup():
    if request.method == 'GET': 
        return jsonify(DatabaseBackup.list_backups())
    success, result = DatabaseBackup.create_backup(request.get_json().get('description', ''))
    return jsonify({'success': success, ('filename' if success else 'error'): result})

@app.route('/api/restore', methods=['POST'])
def api_restore():
    success, msg = DatabaseBackup.restore_backup(request.get_json().get('filename'))
    return jsonify({'success': success, ('message' if success else 'error'): msg})

@app.route('/api/restore_upload', methods=['POST'])
def api_restore_upload():
    """处理上传文件的数据库还原"""
    try:
        if 'file' not in request.files:
            return jsonify({'success': False, 'error': '没有上传文件'}), 400
        
        file = request.files['file']
        if file.filename == '':
            return jsonify({'success': False, 'error': '未选择文件'}), 400
        
        if not file.filename.endswith('.gz'):
            return jsonify({'success': False, 'error': '文件格式不正确'}), 400
        
        file_content = file.read()
        success, msg = DatabaseBackup.restore_from_upload(file_content)
        return jsonify({'success': success, ('message' if success else 'error'): msg})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500

@app.route('/api/backup/<filename>', methods=['DELETE'])
def api_delete_backup(filename):
    conn = None
    try:
        filepath = os.path.join(BACKUP_DIR, filename)
        if os.path.exists(filepath):
            os.remove(filepath)
            conn = get_connection()
            with conn.cursor() as cursor:
                cursor.execute('DELETE FROM backups WHERE filename=%s', (filename,))
                conn.commit()
            return jsonify({'success': True})
        return jsonify({'success': False, 'error': '文件不存在'}), 404
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500
    finally:
        if conn: db_pool.return_connection(conn)

@app.route('/api/today_extremes')
def api_today_extremes():
    conn = None
    try:
        # 参数校验
        query_date = request.args.get('date')
        source = request.args.get('source')
        if not query_date or not source:
            return jsonify({'error': '缺少必需参数: date 或 source'}), 400
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute('SELECT MAX(`price`) as max_price, MIN(`price`) as min_price FROM `prices` WHERE `date`=%s AND `source`=%s', 
                          (query_date, source))
            return jsonify(cursor.fetchone())
    finally:
        if conn: db_pool.return_connection(conn)

def shutdown_handler(signum, frame):
    logger.info('正在关闭服务...')
    if scheduler.running: 
        scheduler.shutdown(wait=True)
    logger.info('服务已关闭')
    os._exit(0)

signal.signal(signal.SIGINT, shutdown_handler)
signal.signal(signal.SIGTERM, shutdown_handler)

if __name__ == '__main__':
    logger.info("服务启动中...")
    if os.path.exists(JOBSTORE_DB):
        try:
            os.remove(JOBSTORE_DB)
            logger.info(f"已清理旧作业存储: {JOBSTORE_DB}")
        except OSError as e:
            logger.error(f"清理作业存储失败: {e}")
    try:
        init_db()
        start_scheduler()
        app.run(host='0.0.0.0', port=3001, use_reloader=False, debug=False)
    except Exception as e:
        logger.critical(f"服务启动失败: {e}", exc_info=True)
        os._exit(1)

